﻿''' <summary>
''' This class is used to help ascertain whether a result value exceeds criteria values in PCodes and Criteria tables
''' </summary>
Public Class clsCriteria
    Implements IDisposable

    Private dictRCodes As Generic.Dictionary(Of String, structRCode)
    Private dictPCodes As Generic.Dictionary(Of String, Double)
    Private dtCriteria As DataTable = Nothing
    Private AutoBDL As Boolean = False

    Private Structure structRCode
        Dim RCode As String
        Dim Calc_Stats As Boolean
        Dim NonDetect_PCode As Boolean
        Dim NonDetect_Result As Boolean
        Dim Detect_Mult As Double
        Sub Initialize()
            RCode = ""
            Calc_Stats = True
            NonDetect_PCode = False
            NonDetect_Result = False
            Detect_Mult = 0.5
        End Sub
    End Structure

    ''' <summary>
    ''' Instantiate class and build lists needed for later criteria determinations
    ''' </summary>
    ''' <param name="Project">WRDB Project</param>
    ''' <param name="TableName">Name of table containing data so can quickly get unique Station ID/PCode pairs for data to be compiled</param>
    ''' <param name="RegGroup">Name of regulatory group (will also match NULL values)</param>
    Public Sub New(Project As clsProject, TableName As String, Optional RegGroup As String = "")

        If RegGroup = "" Then
            Dim ps As New WRDB.Project.clsProjectSettings(Project)
            RegGroup = ps.GetKey(WRDB.Project.modSettings.enumKey.CriteriaRegGroup, "")
        End If

        Dim dr As DbDataReader = Nothing

        'get RCodes

        dictRCodes = New Generic.Dictionary(Of String, structRCode)

        If Project.DB.TableExists("RCodes") Then
            dr = Project.DB.ExecuteReader("SELECT RCodes.RCode, RCodes.Calc_Stats, RCodes.NonDetect_PCode, RCodes.NonDetect_Result, RCodes.Detect_Mult FROM {0} RCodes", Project.FullTableName(clsProject.enumTableTypes.RCodes))
            While dr IsNot Nothing AndAlso dr.Read
                Dim RCode As structRCode
                With RCode
                    .RCode = TestNull(dr.GetValue(0), "")
                    .Calc_Stats = TestNull(dr.GetValue(1), False)
                    .NonDetect_PCode = TestNull(dr.GetValue(2), False)
                    .NonDetect_Result = TestNull(dr.GetValue(3), False)
                    .Detect_Mult = TestNull(dr.GetValue(4), 0.5)
                    dictRCodes.Add(.RCode, RCode)
                End With
            End While
            dr.Close()
        End If

        'get detection limit for pcodes

        dictPCodes = New Generic.Dictionary(Of String, Double)

        If Project.DB.TableExists("PCodes") Then
            dr = Project.DB.ExecuteReader("SELECT PCodes.PCode, PCodes.Det_Limit FROM {0} PCodes", Project.FullTableName(clsProject.enumTableTypes.PCodes))
            While dr IsNot Nothing AndAlso dr.Read
                Dim PCode As String = TestNull(dr.GetValue(0), "")
                Dim DetLimit As Double = TestNull(dr.GetValue(1), Double.MinValue)
                If Not IsDBNull(dr.GetValue(0)) Then
                    dictPCodes.Add(dr.GetString(0), TestNull(dr.GetValue(1), 0.0))
                End If
            End While
            dr.Close()
        End If

        'query Data and PCodes table to get initial criteria limits

        dtCriteria = CompiledCriteria(Project, TableName, RegGroup)

        AutoBDL = Project.AutoBDL

    End Sub

    Private Function CompiledCriteria(Project As clsProject, TableName As String, RegGroup As String) As DataTable

        Dim dtCriteria As New DataTable()
        With dtCriteria
            .Columns.Add("Key", GetType(String)) 'consists of Station ID, PCode, and MonthList (tab separated)
            .Columns.Add("CritGroup", GetType(String))
            .Columns.Add("Station_ID", GetType(String))
            .Columns.Add("PCode", GetType(String))
            .Columns.Add("Monthlist", GetType(String))
            .Columns.Add("Priority", GetType(Double))
            .Columns.Add("MinCrit", GetType(Double))
            .Columns.Add("MaxCrit", GetType(Double))
            .PrimaryKey = New DataColumn() {.Columns("Key")}
        End With

        Dim dr As DbDataReader

        If Not Project.DB.TableExists("PCodes") Then Return dtCriteria

        'add entries for PCode only (in case no station is provided, such as for longitudinal profiles)

        dr = Project.DB.ExecuteReader("SELECT PCodes.PCode, PCodes.Min_Criteria, PCodes.Max_Criteria FROM {0} PCodes", Project.FullTableName(clsProject.enumTableTypes.PCodes))
        While dr IsNot Nothing AndAlso dr.Read
            Dim StaID As String = ""
            Dim PCode As String = TestNull(dr.GetValue(0), "")
            Dim MonthList As String = "" 'will apply to all months
            Dim Key As String = StaID & vbTab & PCode & vbTab & MonthList
            If dtCriteria.Rows.Find(Key) Is Nothing Then
                dtCriteria.Rows.Add(Key, "PCode", StaID, PCode, MonthList, -1, TestNull(dr.GetValue(1), Double.MinValue), TestNull(dr.GetValue(2), Double.MaxValue))
            End If
        End While
        dr.Close()

        'don't do further criteria checks on Paradox projects

        If Project.DB.DatabaseType = clsDB.enumDBType.Paradox Then Return dtCriteria

        'add entries associated with each Station/PCode pair

        dr = Project.DB.ExecuteReader("SELECT DISTINCT Dta.Station_ID, Dta.PCode, PCodes.Min_Criteria, PCodes.Max_Criteria FROM {0} Dta LEFT OUTER JOIN {1} PCodes ON Dta.PCode=PCodes.PCode{2}",
                                      Project.FullTableName(TableName), Project.FullTableName(clsProject.enumTableTypes.PCodes), IIf(Project.QueryOption = clsProject.enumQueryOption.Table AndAlso TableName = clsProject.TempData, String.Format(" WHERE Selected_By = '{0}'", clsApplicationSettings.UserName), ""))
        While dr IsNot Nothing AndAlso dr.Read
            Dim StaID As String = TestNull(dr.GetValue(0), "")
            Dim PCode As String = TestNull(dr.GetValue(1), "")
            Dim MonthList As String = "" 'will apply to all months
            Dim Key As String = StaID & vbTab & PCode & vbTab & MonthList
            If dtCriteria.Rows.Find(Key) Is Nothing Then
                dtCriteria.Rows.Add(Key, "PCode", StaID, PCode, MonthList, -1, TestNull(dr.GetValue(2), Double.MinValue), TestNull(dr.GetValue(3), Double.MaxValue))
            End If
        End While
        dr.Close()

        'criteria table is new, so if it doesn't exist, just exit now using only the pcode criteria

        If Not Project.DB.TableExists("Criteria") OrElse Project.DB.GetRecordCount("Criteria") = 0 Then Return dtCriteria

        'query overrides table for each stations field

        Dim StaFieldList As String = "Station_ID, Station_Type, HUC, Branch_ID, Reach_ID, Agency, Remarks, Other, WaterBodyType, Basin_Name, County_Name, EcoRegion, PlanRegion"

        For Each fld As String In StaFieldList.Split(", ")
            dr = Project.DB.ExecuteReader(
                $"Select DISTINCT 
                    Dta.Station_ID, 
                    Crit.PCode, 
                    Crit.Month_list, 
                    Crit.CritGroup, 
                    Crit.Priority, 
                    Crit.Min_Criteria, 
                    Crit.Max_Criteria 
                FROM 
                    {Project.FullTableName(TableName)} Dta, 
                    {Project.FullTableName(clsProject.enumTableTypes.Criteria)} Crit, 
                    {Project.FullTableName(clsProject.enumTableTypes.Stations)} Stations 
                WHERE 
                    Dta.PCode=Crit.PCode 
                    AND Stations.{fld}=Crit.{fld} 
                    AND Dta.Station_ID=Stations.Station_ID 
                    AND (Crit.RegGroup='{RegGroup}' OR Crit.RegGroup IS NULL)
                    {IIf(Not TableName.StartsWith("(") AndAlso TableName = clsProject.TempData, String.Format(" AND Selected_By = '{0}'", clsApplicationSettings.UserName), "")}")
            While dr IsNot Nothing AndAlso dr.Read
                Dim StaID As String = TestNull(dr.GetValue(0), "")
                Dim PCode As String = TestNull(dr.GetValue(1), "")
                Dim MonthList As String = TestNull(dr.GetValue(2), "")
                Dim CritGroup As String = TestNull(dr.GetValue(3), "")
                Dim Priority As Double = TestNull(dr.GetValue(4), 0)
                Dim Key As String = StaID & vbTab & PCode & vbTab & MonthList
                Dim r As DataRow = dtCriteria.Rows.Find(Key)
                If r Is Nothing Then
                    dtCriteria.Rows.Add(Key, CritGroup, StaID, PCode, MonthList, Priority, TestNull(dr.GetValue(5), Double.MinValue), TestNull(dr.GetValue(6), Double.MaxValue))
                Else
                    'if this sta/pcode combo already exists, replace it only if priority weight is larger
                    If Priority > r("Priority") Then
                        dtCriteria.Rows.Remove(r)
                        dtCriteria.Rows.Add(Key, CritGroup, StaID, PCode, MonthList, Priority, TestNull(dr.GetValue(5), Double.MinValue), TestNull(dr.GetValue(6), Double.MaxValue))
                    End If
                End If
            End While
            dr.Close()
            Threading.Thread.Sleep(0)
        Next
        Return dtCriteria
    End Function

    ''' <summary>
    ''' Look through previously compiled criteria table and get criteria limits if found
    ''' </summary>
    ''' <param name="StationID">Station ID</param>
    ''' <param name="PCode">Parameter Code</param>
    ''' <param name="DateTime">Date and time of observation (in case criteria are month-dependent)</param>
    ''' <param name="RCode">Result Code (note: > is hardcoded to mean too many to count)</param>
    ''' <param name="Result">Numeric value for result; if field is null, this should be Double.MinValue; will return adjusted value suitable for calculations if is ND value</param>
    ''' <param name="MinCriteria">Minimum Criteria value (or Double.MinValue if not specified)</param>
    ''' <param name="MaxCriteria">Maximum Criteria value (or Double.MaxValue if not specified)</param>
    ''' <param name="CriteriaGroup">Name of criteria group found to be applicable (or 'PCode' if not overridden in Criteria table)</param>
    ''' <param name="IsNonDetect">True if RCode indicates is a non-detect value of either type</param>
    ''' <param name="IsCalcStats">True if stats should be calculated using this value</param>
    ''' <returns>True if criteria limits found; False if PCode/Criteria not found or RCode is found and Calc_Stats is false (in which case Double.MinValue is returned for Result)</returns>
    Public Function TryGetCriteria(StationID As String, PCode As String, DateTime As Date, RCode As String, ByRef Result As Double,
                                   ByRef MinCriteria As Double, ByRef MaxCriteria As Double, Optional ByRef CriteriaGroup As String = "",
                                   Optional ByRef IsNonDetect As Boolean = False, Optional ByRef IsCalcStats As Boolean = True, Optional ByRef DetectionLimit As Double = Double.MinValue) As Boolean

        MinCriteria = Double.MinValue
        MaxCriteria = Double.MaxValue

        'must have entry in PCode table

        Dim DetLimit As Double = 0.0
        If Not dictPCodes.TryGetValue(PCode, DetLimit) Then
            CriteriaGroup = "PCode Missing"
            If Result = Double.MinValue Then
                Result = 0
                IsNonDetect = True
                IsCalcStats = False
            End If
            Return False
        End If

        DetectionLimit = DetLimit

        'use RCode to decide if should be included and what result value should be (if ND)

        Dim RCodeRcd As structRCode = Nothing
        If RCode Is Nothing OrElse Not dictRCodes.TryGetValue(RCode, RCodeRcd) Then RCodeRcd.Initialize()

        CriteriaGroup = ""

        If AutoBDL AndAlso RCode = "" AndAlso Result <> Double.MinValue AndAlso Result < DetectionLimit Then

            IsCalcStats = True
            IsNonDetect = True

        ElseIf RCodeRcd.Calc_Stats Then

            IsCalcStats = True

            'adjust result value for non-detect readings

            If Result = Double.MinValue Then 'since was null, regardless of what RCode indicates, must be a factor of detection limit
                Result = DetLimit * RCodeRcd.Detect_Mult
            ElseIf RCodeRcd.NonDetect_PCode AndAlso DetLimit > 0 Then
                Result = DetLimit * RCodeRcd.Detect_Mult
            ElseIf RCodeRcd.NonDetect_Result Then
                DetectionLimit = Result
                Result *= RCodeRcd.Detect_Mult
            ElseIf RCode = ">" And Result = Double.MinValue Then 'hard-wired: if result is null and RCode is >, will trigger exceedance (really need another field in RCodes table for this)
                Result = Double.MaxValue
            End If

            IsNonDetect = RCodeRcd.NonDetect_PCode Or RCodeRcd.NonDetect_Result

        Else

            IsCalcStats = False
            Return False

        End If

        If IsCalcStats Then

            'find applicable criteria; there may be multiple matches for Station ID and PCode due to different month list entries or priorities; look at them individually

            Dim CritRows() As DataRow = dtCriteria.Select(String.Format("Station_ID='{0}' AND PCode='{1}'", StationID.Replace("'", "''"), PCode))
            Dim maxpriority As Double = Double.MinValue

            If CritRows Is Nothing Then
                Return False
            Else
                For Each rCrit As DataRow In CritRows
                    Dim Priority As Double = TestNull(rCrit("Priority"), Double.MinValue + 1)
                    If Priority >= maxpriority Then
                        maxpriority = Priority
                        If TestNull(rCrit("MonthList"), "") = "" Then 'matches all dates
                            MinCriteria = rCrit("MinCrit")
                            MaxCriteria = rCrit("MaxCrit")
                            CriteriaGroup = rCrit("CritGroup")
                        Else
                            Dim arMonths() As String = CStr(rCrit("MonthList")).Split(",")
                            For Each m As String In arMonths
                                If IsNumeric(m) AndAlso DateTime.Month = Val(m) Then
                                    MinCriteria = rCrit("MinCrit")
                                    MaxCriteria = rCrit("MaxCrit")
                                    CriteriaGroup = rCrit("CritGroup")
                                End If
                            Next
                        End If
                    End If
                Next
                Return True
            End If

        End If

    End Function

#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                If dtCriteria IsNot Nothing Then dtCriteria.Dispose()
            End If
        End If
        Me.disposedValue = True
    End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
